import pandas as pd
import psycopg2
from psycopg2 import sql
from dataclasses import dataclass
import json

@dataclass
class Labels:
    rack: str
    department: str

@dataclass
class InstanceInfoResult:
    targets: list
    labels: Labels

def csv_to_postgres(csv_file_path, table_name, columns):
    """
    从CSV文件读取数据并写入到PostgreSQL表中。

    :param csv_file_path: CSV文件路径
    :param table_name: 目标表名
    :param columns: 列名列表
    """
    # 读取CSV文件
    df = pd.read_csv(csv_file_path, header=None, names=columns)

    # 连接到PostgreSQL数据库
    conn = psycopg2.connect(
        host=db_host,
        dbname=db_name,
        user=db_user,
        password=db_passwd
    )
    cursor = conn.cursor()

    # 动态生成插入语句
    placeholders = ', '.join(['%s'] * len(columns))
    insert_query = sql.SQL(
        "INSERT INTO {table} ({columns}) VALUES ({placeholders})"
    ).format(
        table=sql.Identifier(table_name),
        columns=sql.SQL(', ').join(map(sql.Identifier, columns)),
        placeholders=sql.SQL(placeholders)
    )
    print(insert_query)

    # 批量插入数据
    data_tuples = [tuple(x) for x in df[columns].values]
    print(data_tuples)
    cursor.executemany(insert_query, data_tuples)

    # 提交事务
    conn.commit()

    # 关闭连接
    cursor.close()
    conn.close()


def instance_info(filename):
    table_name = 'instance_info'
    columns = ['ip', 'rack', 'department', 'architecture', 'distribution', 'description', 'status']
    csv_to_postgres(filename, table_name, columns)

def rack_static_metrics_consumption(filename):
    table_name = 'rack_static_metrics_consumption'
    columns = ['rack', 'power_value']
    csv_to_postgres(filename, table_name, columns)

def server_info(filename):
    table_name = 'server_info'
    columns = ['server_ip', 'rack', 'username', 'password', 'department', 'bmc_ip', 'bmc_user', 'bmc_pass']
    csv_to_postgres(filename, table_name, columns)

# 连接到数据库并执行查询
def fetch_instance_info(department):
    try:
        conn = psycopg2.connect(
            host=db_host,
            dbname=db_name,
            user=db_user,
            password=db_passwd
        )
        cur = conn.cursor()

        # SQL查询
        query = f"""
        SELECT
            array_agg(ip) AS targets,
            rack,
            department
        FROM
            instance_info
        WHERE department = '{department}'
        GROUP BY
            rack,
            department
        ORDER BY
            rack,
            department;
        """
        print(query)

        cur.execute(query)
        rows = cur.fetchall()

        print(rows)
        # 将结果封装到对象中
        results = [InstanceInfoResult([ip + ':9290' for ip in row[0]], Labels(row[1], row[2])) for row in rows]

        # 关闭游标和连接
        cur.close()
        conn.close()

        return results
    except Exception as e:
        print(f"An error occurred: {e}")
        return []

# 将对象列表转换为JSON格式输出
# def results_to_json(results):
    # return json.dumps([result.__dict__ for result in results], indent=4 ,ensure_ascii=False)
from dataclasses import asdict

def results_to_json(results):
    results_dict = [
        {
            'targets': result.targets,
            'labels': asdict(result.labels)
        }
        for result in results
    ]
    return json.dumps(results_dict, indent=4, ensure_ascii=False)


# 主函数
def main():
    department_out_map = {'行业产品部': 'industry_target.json',
                          '应用产品部': 'application_target.json',
                          'CTO办公室' : 'cto_target.json',
                          '平台产品部': 'platform_target.json',
                          '测试部': 'test_target.json',
                          '云轨智联': 'yungui_target.json'}
    # 平台产品部中有 10.10.24.101 被占用 需要手动改成 :9291
    for department, filename in department_out_map.items():
        results = fetch_instance_info(department)
        json_output = results_to_json(results)

        # 将JSON字符串写入文件
        with open('ipmi_file/'+filename, 'w', encoding='utf-8') as file:
            file.write(json_output)




if __name__ == '__main__':
    # instance_info('ipmi_file/cto_instance_info.csv')
    # main()
    server_info('ipmi_file/server_info_app.csv')